The calendar slicer is a specialized slicer for date / time columns coming from a SQL database, especially where the classic date / time data groupings (quarter, semester, year, month, week) don't exist. The slicer enables you to filter the query by these groupings, without the need to construct any complex formulations. This is an excellent solution for users who are directly querying a SQL database, and cannot manipulate the database and don't want to spend time creating the relevant formulations.
Pyramid data modeling enables users to construct these hierarchical date/ time groupings when designing the ETL, and then add those elements to the query in Discover. However, this solution is only relevant to those users who construct the model in Pyramid; but many users query the database directly. In a direct querying scenario, the user may want to add 'years' to the query, while the database only contains a flat date / time hierarchy consisting of date keys. How can this user add 'years' to the query?
Pyramid offers 2 ways solutions for this requirement: on-the-fly date part calculations, and calendar slicers. While date part calculations produce custom columns which can be added to any part of the query, the calendar slicer is used to filter the query by specified dates or time periods, date ranges, and date formulations. From the calendar slicer, the user selects the required date(s) or time period and the query is filtered on-the-fly, without creating custom columns.
Note: calendar slicers are supported only for SQL models. This functionality is not currently supported for MS OLAP, Tabular, or SAP BW.
How to Create a Calendar Slicer
A calendar slicer can be created using a date/ time column only. Add the date/ time hierarchy to the Filters zone; this produces a slicer which is added to the canvas.
When you click on the slicer, the calendar picker will be displayed instead of a drop-down list. Continue reading to learn how to navigate the calendar picker.
Navigate the Calendar Picker
Calendar Type
The calendar slicer features 3 different calendar pickers, each designed to meet a different set of user requirements.
Simple Picker
The Simple calendar picker enables the selection of one or more elements. To select more than 1 element, ensure that Multi Select is enabled; you can then select multiple elements from any level of granularity. The query will be filtered by the element(s) that you've selected.
Range Picker
Use the Range picker to filter the query by a date range, beginning with the 'From' date, up to and including to 'To' date. Select the required level of granularity, then select the start date from the left, and the end date from the right.
Formula Picker
Filter the query by a formula based on the selected date element. Start by selecting the granularity level, then select the date element, then choose the required function.
Granularity
Rather than displaying dates only, the calendar picker allows you to specify the desired level of granularity, meaning you can choose to display any of the date time groupings in the calendar and make your selections accordingly.
Years
Select any years by which you want to filter the query. This example will filter the query to display data for 2010:
Semesters
Select the required semester(s) from within the relevant year(s). This example will filter the query by semester 2 of 2008 and 2009:
Quarters
Select any quarters from the relevant year(s) to filter the query accordingly. This example will filter the query by Q4 of 2008, 2009. and 2010:
Months
Choose any months from within the relevant year(s). This example will filter the query by July and August, 2010:
Weeks
Choose the relevant weeks from the required year. In this example, the query will be filters by weeks 1, 2, and 3 of 2009:
Dates
Select any dates from within the relevant year(s) and month(s). In this example, the query will be filtered by January 3,4,17,18, and 31, 2009:
Calendar Selection
The calendar picker allows selections for dates that exist in the database only. Dates and periods for which there is no data are grayed out. To make a selection from the calendar, start by selecting the required level of granularity, then select the required date or time period.
To assist in making a selection, use the back and forward arrows to move to the next or previous period, and use the up and down buttons to jump to a different month or year.
Back and Forward Buttons
Use the back and forward buttons to navigate through the current level of granularity. For example, at the date level, click the forward arrow to go to the next month, or the back arrow to go to the previous month.
At the week level, click forward to go to the next year, or back to go to the previous year.
Up and Down Buttons
Use the up and down buttons to jump to a different month or year for the current level of granularity. Up buttons indicate that you can go up a level, while down arrows indicate that you can go down a level, without making a filter selection.
Examples
To follow these examples, use the Sample Demo data model.
Dates Granularity
From the date level, you will see the currently selected year and month. Click the up button once:
You will now see the months (for which there is data) in the currently selected year. Each month displays a down arrow, indicating that if you click on a month, you will see the date level:
Click the up button again to see the years, and then select 2010:
You will now see the months in 2010. Select September:
You will now see the dates for 2010, September and you can make a filter selection of dates:
Weeks Granularity
From the weeks, months, quarters, and semesters levels, the up button will show you the years. Set the granularity to Weeks, and click the up button.
You will see the list of years, accompanied by a down arrow. Select 2009:
You will now see all the weeks in 2009, and you can make a filter selection of weeks:
Multi Select
The multi select option is supported for the Simple calendar picker, allowing you to select multiple date/ time elements You can select multiple elements from different granularity levels.
Multi Select is enabled by default; to disable it, deselect its checkbox:
Multi Select Examples
In this example, all Thursdays in September 2010 were selected, along with the entire 4th week of the same month:
In this example, Q4 was selected from 2008, 2009, and 2010:
Quick Selection
The quick selection options provide a shortcut selection for filtering by the current, first, or last date/ time period in the current level of granularity. To male a quick selection, start by choosing the granularity level as usual. Then, instead of making a selection from the calendar, choose a quick selection.
Current: depending on the given level of granularity, selects today's date (if it exists in the system) or the current year, semester, quarter, month, or week. If the current date/ time period doesn't exist in the system, the last date/ time period will be selected instead.
First: selects the first date/ time period in the system according to the selected level of granularity. If the granularity is set to Dates, the first date in the system will be selected. If the granularity is set to months, for instance, the first month in the system will be selected.
First Date Examples
Here, the first date in the system was selected:
Here, the granularity is set to Weeks, so the first week was selected:
Last: selects the last date/ time period in the system according to the selected level of granularity. If the granularity is set to Dates, the last date in the system is selected. If the granularity is set to years, the last year in the system is selected.
Last Date Examples
Here, the last date in the system was selected:
Now the last semester in the system was selected:
